﻿//----------------------------------------------------
//    版权：
//    作者：
//建立时间：2012-09-11 00:00:33
//    描述：
//----------------------------------------------------
using System;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.Collections.Generic;


using WinnerCMS.Common;
using WinnerCMS.Model;
using EntityFramework.Extensions;
namespace WinnerCMS.Data
{
    /// <summary>
    /// 数据访问层CMS_Orders。
    /// </summary>
    public class OrdersDal
    {
        #region  是否存在
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool Exists(int Id)
        {
            using (Entities db = new Entities())
            {
                return db.Orders.Any(x => x.Id == Id);
            }
        }
        #endregion

        #region  添加数据

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <param name="cartGuid"></param>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public int Add(Order model, string cartGuid, int goodsId)
        {
            SqlParameter[] arrParameter = new SqlParameter[17];

            arrParameter[0] = new SqlParameter("OrderNum", SqlDbType.NVarChar, 50);
            arrParameter[1] = new SqlParameter("UserName", SqlDbType.NVarChar, 20);
            arrParameter[2] = new SqlParameter("NeedInvoice", SqlDbType.TinyInt, 1);
            arrParameter[3] = new SqlParameter("Invoiced", SqlDbType.TinyInt, 1);
            arrParameter[4] = new SqlParameter("Remark", SqlDbType.NText);
            arrParameter[5] = new SqlParameter("ContacterName", SqlDbType.NVarChar, 50);
            arrParameter[6] = new SqlParameter("Address", SqlDbType.NVarChar, 255);
            arrParameter[7] = new SqlParameter("ZipCode", SqlDbType.NVarChar, 50);
            arrParameter[8] = new SqlParameter("Mobile", SqlDbType.NVarChar, 50);
            arrParameter[9] = new SqlParameter("Phone", SqlDbType.NVarChar, 50);
            arrParameter[10] = new SqlParameter("Email", SqlDbType.NVarChar, 50);
            arrParameter[11] = new SqlParameter("PaymentType", SqlDbType.Int, 4);
            arrParameter[12] = new SqlParameter("DeliverType", SqlDbType.Int, 4);
            arrParameter[13] = new SqlParameter("ChargeDeliver", SqlDbType.Money, 8);
            arrParameter[14] = new SqlParameter("InvoiceContent", SqlDbType.NText);
            arrParameter[15] = new SqlParameter("CartGUID", SqlDbType.NVarChar, 50);
            arrParameter[16] = new SqlParameter("GoodsID", SqlDbType.Int, 4);


            arrParameter[0].Value = model.OrderNum;
            arrParameter[1].Value = model.UserName;
            arrParameter[2].Value = model.NeedInvoice;
            arrParameter[3].Value = model.Invoiced;
            arrParameter[4].Value = model.Remark;
            arrParameter[5].Value = model.ContacterName;
            arrParameter[6].Value = model.Address;
            arrParameter[7].Value = model.ZipCode;
            arrParameter[8].Value = model.Mobile;
            arrParameter[9].Value = model.Phone;
            arrParameter[10].Value = model.Email;
            arrParameter[11].Value = model.PaymentType;
            arrParameter[12].Value = model.DeliverType;
            arrParameter[13].Value = model.ChargeDeliver;
            arrParameter[14].Value = model.InvoiceContent;
            arrParameter[15].Value = string.IsNullOrEmpty(cartGuid) == true ? string.Empty : cartGuid;
            arrParameter[16].Value = goodsId;


            return (int)SQLBaseV2.ExecuteScalar("P_CMS_Orders_Add", CommandType.StoredProcedure, arrParameter);
        }

        #endregion

        #region  修改数据
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        public void Edit(Order model)
        {
            using (Entities db = new Entities())
            {
                db.Orders.Attach(model);
                db.Entry(model).State = EntityState.Modified;

                var orderlist = db.OrderList.Where(x => x.OrderId == model.Id);
                if (orderlist.Any())
                {
                    model.MoneyGoods = orderlist.Sum(x => x.Price);
                    model.MoneyTotal = model.MoneyGoods + model.ChargeDeliver;
                }

                db.SaveChangesAsync().Wait();
            }
        }
        #endregion

        #region  删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        public void Delete(string Ids)
        {
            using (Entities db = new Entities())
            {
                string sql = string.Format(@"
DELETE FROM CMS_Orders WHERE Id IN ({0}) 
DELETE FROM CMS_OrderList WHERE OrderId IN ({0})
DELETE FROM CMS_PriceChanges WHERE OrderId IN ({0})
DELETE FROM CMS_Delivery WHERE OrderId IN ({0})
DELETE FROM CMS_BankrollList WHERE OrderId IN ({0})
DELETE FROM CMS_Invoice WHERE OrderId IN ({0})", Ids);

                db.Database.ExecuteSqlCommand(sql);
            }
        }
        #endregion

        #region  加载数据
        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Order GetInfo(int Id)
        {
            using (Entities db = new Entities())
            {
                return db.Orders.FirstOrDefault(x => x.Id == Id);
            }
        }
        #endregion

        #region 取得用户的订单
        /// <summary>
        /// 取得用户的订单
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public IList<Order> GetOrdersWithUserName(string userName)
        {
            using (Entities db = new Entities())
            {
                var q = from x in db.Orders
                        where x.UserName == userName && x.Status == 0 && x.DeliverStatus == 0 && x.PayStatus == 0
                        select new
                        {
                            Id = x.Id,
                            x.OrderNum
                        };
                return q.ToList().Select(x => new Order() { Id = x.Id, OrderNum = x.OrderNum }).ToList();
            }
        }
        #endregion

        #region 合并订单
        /// <summary>
        /// 合并订单
        /// </summary>
        /// <param name="OrderIdZ">主订单</param>
        /// <param name="OrderIdC">从订单</param>
        public void OrdersMerge(int OrderIdZ, int OrderIdC)
        {
            using (Entities db = new Entities())
            {
                db.OrderList
                  .Where(x => x.OrderId == OrderIdC)
                  .Update(x => new OrderList { OrderId = OrderIdZ });
                //计算货物金额合计
                var order = db.Orders.FirstOrDefault(x => x.Id == OrderIdZ);
                if (order != null)
                {
                    var moneyGoods = db.OrderList
                        .Where(x => x.OrderId == OrderIdZ)
                        .Sum(x => x.Price);
                    //计算总计
                    db.Orders
                        .Where(x => x.Id == OrderIdZ)
                        .Update(x => new Order
                        {
                            MoneyGoods = moneyGoods,
                            MoneyTotal = order.MoneyGoods + order.ChargeDeliver
                        });
                }
                db.Database.ExecuteSqlCommand("DELETE FROM CMS_Orders WHERE Id={0}", OrderIdC);
                db.Database.ExecuteSqlCommand("DELETE FROM CMS_PriceChanges WHERE OrderId={0}", OrderIdC);
            }
        }
        #endregion

        #region 检查订单是否会超出库存
        /// <summary>
        /// 检查订单是否会超出库存
        /// </summary>
        /// <param name="cartGuid"></param>
        /// <returns></returns>
        public int[] CheckOutStock(string cartGuid)
        {
            using (Entities db = new Entities())
            {
                return db.Database.SqlQuery<int>(@"SELECT SUM(Quantity)-SUM(Stocks) FROM CMS_Goods A inner join CMS_ShoppingCarts B ON A.Id = B.GoodsID
WHERE CartGUID={0} GROUP BY GoodsID", cartGuid).ToArray();
            }
        }
        #endregion

        #region 绑定分页数据
        /// <summary>
        /// 绑定分页数据
        /// </summary>
        /// <param name="sort"></param>
        /// <param name="keyWord"></param>
        /// <param name="status"></param>
        /// <param name="deliverStatus"></param>
        /// <param name="pp"></param>
        /// <returns></returns>
        public IList<Order> BindList(string sort, string keyWord, int status, int deliverStatus, PagingParam pp)
        {
            using (Entities db = new Entities())
            {
                var query = from x in db.Orders
                            select x;
                if (status != -1)
                {
                    query = query.Where(x => x.Status == status);
                }
                if (deliverStatus != -1)
                {
                    query = query.Where(x => x.DeliverStatus == deliverStatus);
                }
                switch (sort)
                {
                    case "OrderNum":
                        query = query.Where(x => x.OrderNum == keyWord);
                        break;
                    case "UserName":
                        query = query.Where(x => x.UserName == keyWord);
                        break;
                }

                pp.RecordCount = query.Count();

                return query.OrderByDescending(x => x.Id).Skip(pp.Skip).Take(pp.PageSize).ToList();
            }
        }
        #endregion
    }
}